package hk.ust.cse.comp4981;

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
public class jdbcmysql { 
	  private Connection con = null; //Database objects 
	  private Statement stat = null; 
	  private ResultSet rs = null; 
	  private PreparedStatement pst = null; 
	  
	  //sql statement
	  private String insertdbSQL_ny = "insert into ny_tweet(user_id, country_id, time, content) VALUES(?,?,?,?)"; 
	  private String insertdbSQL_sf = "insert into sf_tweet(user_id, country_id, time, content) VALUES(?,?,?,?)"; 
	  private String insertdbSQL_user = "insert into twitter_user(username, favo_count, followers_count, frd_count, active_count) VALUES(?,?,?,?,?)";
	  private String insertdbSQL_event = "insert into event(event_time, event, country_id) VALUES(?,?,?)";
	  private String deletedbSQL_sf = "delete from sf_tweet where tweet_id = ?";
	  private String deletedbSQL_ny = "delete from ny_tweet where tweet_id = ?";
	  private String selectSQL_sf = "select user_id from sf_tweet where tweet_id = ?";
	  private String selectSQL_ny = "select user_id from ny_tweet where tweet_id = ?";
	  private String selectSQL_user = "select max(active_count) from twitter_user";
	  private String deletedbSQL_user = "delete from twitter_user where user_id = ?";

	  
	  public jdbcmysql() 
	  { 
	    try { 
	      Class.forName("com.mysql.jdbc.Driver"); 
	      con = DriverManager.getConnection( 
	      "jdbc:mysql://localhost/pan1?useUnicode=true&characterEncoding=Big5", 
	      "root","123123"); 
	      //get connection
	      //localhost: host name
	      //pan1: database name
	      //root: username
	      //123123: password
	    } 
	    catch(ClassNotFoundException e) 
	    { 
	      System.out.println("DriverClassNotFound :"+e.toString()); 
	    }//may exist sql exception 
	    catch(SQLException x) { 
	      System.out.println("Exception :"+x.toString()); 
	    } 
	    
	  } 
	  
	  //insert to ny_tweet table
	  public void insertTable_ny(int userid, int countryid, String time, String content) 
	  { 
	    try 
	    { 
	      pst = con.prepareStatement(insertdbSQL_ny, Statement.RETURN_GENERATED_KEYS); 
	      
	      pst.setInt(1, userid); 
	      pst.setInt(2, countryid);
	      pst.setString(3, time);
	      pst.setString(4, content);;
	      
	      pst.executeUpdate(); 
	      rs = pst.getGeneratedKeys();
	      
	      //delete the n - 1000th row
	      int lastid = 0;
	      if(rs.next()){
	    	  lastid = rs.getInt(1); 
	      }
	      if (lastid > 1000){
	    	  int delid = lastid - 1000;
	    	  SelectTable_ny(delid, 0);
	    	  deleteRecord_ny(delid);
	      }
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("InsertDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    } 
	  }
	  
	  //insert to sf_tweet table
	  public void insertTable_sf(int userid, int countryid, String time, String content) 
	  { 
	    try 
	    { 
	      pst = con.prepareStatement(insertdbSQL_sf, Statement.RETURN_GENERATED_KEYS); 
	      
	      pst.setInt(1, userid); 
	      pst.setInt(2, countryid);
	      pst.setString(3, time);
	      pst.setString(4, content);;
	      
	      pst.executeUpdate();
	      rs = pst.getGeneratedKeys();
	      
	      //delete the (n - 1000)th row
	      int lastid = 0;
	      if(rs.next()){
	    	  lastid = rs.getInt(1); 
	      }
	      if (lastid > 1000){
	    	  int delid = lastid - 1000;
	    	  SelectTable_sf(delid, 0);
	    	  deleteRecord_sf(delid);
	      }
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("InsertDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    } 
	  }
	  
	  //insert to twitter_user table
	  public int insertTable_user(String username, int favo_count, int followers_count, int frd_count, int lastid) 
	  { 
	    try 
	    { 
		  pst = con.prepareStatement(selectSQL_user, Statement.RETURN_GENERATED_KEYS); 
		  rs = pst.executeQuery(); 
		  rs.next();
		  
		  //counting active user
		  int active_count = rs.getInt("max(active_count)");
		  if(active_count < 1){
			  active_count = 0;
		  }
		  int test_active = favo_count / 3;
	      if(favo_count > 30 && test_active < followers_count){
	    	  active_count++;
	      }
	      
	      pst = con.prepareStatement(insertdbSQL_user, Statement.RETURN_GENERATED_KEYS); 
	      
	      pst.setString(1, username); 
	      pst.setInt(2, favo_count);
	      pst.setInt(3, followers_count);
	      pst.setInt(4, frd_count);
	      pst.setInt(5, active_count);;
	      
	      pst.executeUpdate();
	      rs = pst.getGeneratedKeys();
	      if(rs.next()){
	    	  lastid = rs.getInt(1); 
	      }
	      
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("InsertDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    }
	    return lastid;
	  }
	  
	  //insert to event table
	  public void insertTable_event(String time, String content, int countryid) 
	  { 
	    try 
	    { 
	      pst = con.prepareStatement(insertdbSQL_event, Statement.RETURN_GENERATED_KEYS); 
	       
	     
	      pst.setString(1, time);
	      pst.setString(2, content);
	      pst.setInt(3, countryid);;
	      
	      pst.executeUpdate();
	     
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("InsertDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    } 
	  }
	  
	//find the (n - 1000)th row in ny_tweet table
	  public void SelectTable_ny(int tweet_id, int user_id){
		  try 
		    { 
			  pst = con.prepareStatement(selectSQL_ny, Statement.RETURN_GENERATED_KEYS); 
			  pst.setInt(1, tweet_id);; 
		      rs = pst.executeQuery(); 
		      rs.next();
		      user_id = rs.getInt("user_id");
		      deleteRecord_user(user_id);
		    } 
		    catch(SQLException e) 
		    { 
		      System.out.println("DropDB Exception :" + e.toString()); 
		    } 
		    finally 
		    { 
		      Close(); 
		    } 
	  }
	  
	  //find the (n - 1000)th row in sf_tweet table
	  public void SelectTable_sf(int tweet_id, int user_id){
		  try 
		    { 
			  pst = con.prepareStatement(selectSQL_sf, Statement.RETURN_GENERATED_KEYS); 
			  pst.setInt(1, tweet_id);; 
		      rs = pst.executeQuery(); 
		      rs.next();
		      user_id = rs.getInt("user_id");
		      deleteRecord_user(user_id); //delete the corresponding user
		    }
		    catch(SQLException e) 
		    { 
		      System.out.println("DropDB Exception :" + e.toString()); 
		    } 
		    finally 
		    { 
		      Close(); 
		    } 
	  }
	  
	  //delete row in ny_tweet table
	  public void deleteRecord_ny(int tweet_id) 
	  { 
	    try 
	    { 
	    	pst = con.prepareStatement(deletedbSQL_ny, Statement.RETURN_GENERATED_KEYS); 
		    pst.setInt(1, tweet_id);;
		      
		    pst.executeUpdate();
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("DeleteDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    } 
	  }
	  
	  //delete row in sf_tweet table
	  public void deleteRecord_sf(int tweet_id) 
	  { 
	    try 
	    { 
	    	pst = con.prepareStatement(deletedbSQL_sf, Statement.RETURN_GENERATED_KEYS); 
		    pst.setInt(1, tweet_id);;
		      
		    pst.executeUpdate();
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("DeleteDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    } 
	  }
	  
	  //delete row in twitter_user table
	  public void deleteRecord_user(int user_id) 
	  { 
	    try 
	    { 
	    	pst = con.prepareStatement(deletedbSQL_user, Statement.RETURN_GENERATED_KEYS); 
		    pst.setInt(1, user_id);;
		      
		    pst.executeUpdate();
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("DeleteDB Exception :" + e.toString()); 
	    } 
	    finally 
	    { 
	      Close(); 
	    } 
	  }
	  
	  //close the connection
	  private void Close() 
	  { 
	    try 
	    { 
	      if(rs!=null) 
	      { 
	        rs.close(); 
	        rs = null; 
	      } 
	      if(stat!=null) 
	      { 
	        stat.close(); 
	        stat = null; 
	      } 
	      if(pst!=null) 
	      { 
	        pst.close(); 
	        pst = null; 
	      } 
	    } 
	    catch(SQLException e) 
	    { 
	      System.out.println("Close Exception :" + e.toString()); 
	    } 
	  } 
	  
	 
	 
	}